#!/usr/bin/env python3
# -*- coding: utf-8 -*-
import os
import pandas as pd
import numpy as np
from file import mkdir
from unit import dateneedupdate
from tqdm import tqdm
from database import con, last, inster, select, select_count


# 计算涨速
def setup(df, code, type, sps=[5, 10, 20, 30, 60]):
    pbar = tqdm()
    try:
        format = "%Y%m%d"
        needupdate = False
        startDate = ""
        lastdata = last(
            "kirogi" + type, fitter=("code='%s'" % code), orderby=["date"], asc=False
        )
        if lastdata == None:
            needupdate = True
        else:
            startDate = lastdata["date"]
            needupdate = dateneedupdate(startDate, format)
        if needupdate == False:
            pbar.set_description("kirogi_setup_code:%s 不需要更新" % code)
            return
        result = []
        df[["close"]] = df[["close"]].astype(float)
        temdf = df
        tdf = pd.DataFrame(result)
        for sp in sps:
            temdf["sp_" + str(sp)] = df["close"].shift(sp)
            tdf["speed" + str(sp)] = temdf["close"] / temdf["sp_" + str(sp)]

        tdf["code"] = df["code"]
        tdf["date"] = df["date"]
        inster(
            "kirogi" + type,
            columns=[
                "date",
                "code",
                "speed5",
                "speed10",
                "speed20",
                "speed30",
                "speed60",
            ],
            df=tdf,
        )
        path = os.path.join(
            os.path.expanduser("~"), ("stockdate/cvs/kirogi/etf/setup"), code + ".csv"
        )
        tdf.to_csv(path)
        pbar.set_description("kirogi_setup_code:%s 更新完成" % code)
        pbar.close()
    except Exception as e:
        print(e)


# 涨速排名 并归一化
def etf_signal(sps=[5, 10, 20, 30, 60]):
    pbar = tqdm()
    for sp in sps:
        pbar.set_description("kirogi_etf_signal:%s " % sp)
        tableName = ("kirogi_etf_signal_%s" % sp)
        createkirogiSignalTable(tableName)
        lastdate = lastdate_kirogiSignalTable(tableName) 
        if lastdate == None:
            lastdate = "00000000"
        path = os.path.join(
                os.path.expanduser("~"), "stockdate/cvs/kirogi/etf/signal/speed" + str(sp)
            )
        sql = ("""
            select date from kirogietf
            where date >"%s"
            GROUP by  date
            order by date  
            """ % lastdate)

        datedf = pd.read_sql_query(sql = sql, con=con)
        for index, row in datedf.iterrows():
            
            strdate = row["date"]
            pbar.set_description("kirogi_etf_signal:sp:%s date:%s " % (sp,strdate))
            count = select_count(
                "kirogietf", fitter=(" date=%s and speed%s!='nan'" % (strdate, sp))
            )
            mkdir(path)
            sortsql = """
                select code,date,speed%s  from kirogietf
                WHERE date="%s" and speed%s!='nan'
                ORDER  by speed%s
                """ % (sp,strdate,sp, sp)
            sortdf = pd.read_sql_query(sql=sortsql, con=con)
            sortdf["sort"] = sortdf.index
            sortdf["count"] = count
            sortdf["signal"] = sortdf["sort"] / count
            file = os.path.join(path, strdate + ".csv")
            inster(table=tableName,columns=["code","date","sort","count","signal"],df=sortdf)
            sortdf.to_csv(file)
    pbar.close()      


def createkirogiSignalTable(name):
    cur = con.cursor()
    sql = (
        """
    CREATE TABLE IF NOT EXISTS "%s" -- 股票日数据
     (
        "date" TEXT, -- 时间
        "code" TEXT, -- 股票代码
        "sort"	INTEGER, -- 排序
        "count" INTEGER, -- 总数
        "signal" NUMERIC, -- 信号量
        PRIMARY KEY("code","date")
     )
    """
        % name
    )
    cur.execute(sql)


def lastdate_kirogiSignalTable(name):
    cur = con.cursor()
    sql = (
        """
        select max(date) as date  from %s 
        """ % name)
    cur.execute(sql)
    return cur.fetchone()["date"]
